package com.sinai.mshab.client.ui.filter;

import java.util.ArrayList;
import java.util.HashMap;

import com.google.gwt.core.client.GWT;
import com.google.gwt.user.client.rpc.AsyncCallback;
import com.google.gwt.user.client.rpc.ServiceDefTarget;
import com.google.gwt.user.client.ui.ClickListener;
import com.google.gwt.user.client.ui.HorizontalPanel;
import com.google.gwt.user.client.ui.Label;
import com.google.gwt.user.client.ui.ListBox;
import com.google.gwt.user.client.ui.Widget;
import com.sinai.mshab.client.services.Service;
import com.sinai.mshab.client.services.ServiceAsync;
import com.sinai.mshab.client.ui.FormComponent;
import com.sinai.mshab.client.ui.aggregate.FormData;
import com.sinai.mshab.client.ui.aggregate.TableComponent;
import com.sinai.mshab.client.ui.aggregate.VerticalComponentList;

/**
 * This table will show all the records specified by Key. For example all the
 * patients/clients in addition, this information can be FilterAggregateed by
 * joining Key's table on additional tables in the database given criteria
 * specified by FilterAggregate objects.
 * 
 * @author serge
 */
public abstract class FilteredTable extends VerticalComponentList {

	/** This list stores all the FilterAggregates associated with this table */
	protected ArrayList filterAggregates = new ArrayList();

	/** This list stores all the columns in this table */
	protected ArrayList column_names = new ArrayList();

	/** The sortable table */
	protected TableComponent table;
	
	/**
	 * The prefix that appears in the URL when the user clicks on a database
	 * record. The default value will be "record="
	 */
	protected String historyChangePrefix = "record=";

	/** The dropdown list of possible fields to sort by */
	protected ListBox sort_by;

	/** The dropdown list of possible fields to sort by */
	protected ListBox sort_asc_desc;

	/** The dropdown for the number of records per page to show */
	protected ListBox records_per_page;

	/**
	 * These variables will control which part of data to display and how much
	 * of it
	 */
	private int start_record = 1;

	private int record_chunk = 50;

	/** Label which tells the user which records are being shown */
	protected Label showing_records;

	protected HorizontalPanel record_navigation;

	public static ArrayList columns_seen_sofar;
	
	protected HashMap fieldsToCustomFilters;

	/**
	 * Creates a new filtered table. A simple table will have a run button at
	 * the bottom and no sorting criteria or adding of categories.
	 * 
	 * @param simple
	 *            specify if this table is simple or not. Simple tables are used
	 *            primarily for custom filters
	 */
	public FilteredTable() {
		super("");
	}

	/**
	 * This will construct the sql based on the column names and the
	 * FilterAggregates available.
	 * 
	 * @param the
	 *            sql that will retreive the appropriate data for this table
	 */
	public String constructSQL() {

		String key_table_name = FormData.getKey().getTableName();

		String sql = "SELECT ";

		// we want to remember the columns we are getting back
		// from the server to make sure we dont ask for duplicates
		columns_seen_sofar = new ArrayList();

		// appending prepared columns
		for (int i = 0; i < column_names.size(); i++) {
			String column = (String) column_names.get(i);
			String show = column.replace('.', '_');

			// dont need a comma for the first value.
			if (i == 0) {
				sql += " " + column + " " + show + " ";
				columns_seen_sofar.add(column + " " + show);
			}
			// need commas between column names for all other values.
			else if (!columns_seen_sofar.contains(column + " " + show)) {
				sql += " ," + column + " " + show + " ";
				columns_seen_sofar.add(column + " " + show);
			}
		}

		// appending FilterAggregate columns
		for (int i = 0; i < filterAggregates.size(); i++) {
			FilterAggregate filterAgg = (FilterAggregate) filterAggregates
					.get(i);
			String column = filterAgg.getColumns();

			// append the column to the sql clause
			if (column.trim().length() > 0) {

				// the column comes with a ',' infront if we had no predefined
				// columns this might cause an sql syntax problem.
				if (sql.trim().equals("SELECT")) {
					sql += column.substring(column.indexOf(',') + 1) + " ";
				} else {
					sql += column + " ";
				}
			}
		}

		sql += "FROM " + key_table_name + " ";

		// appending FilterAggregate columns
		ArrayList seen_before = new ArrayList();
		for (int i = 0; i < filterAggregates.size(); i++) {
			FilterAggregate FilterAggregate = (FilterAggregate) filterAggregates
					.get(i);
			String join = FilterAggregate.getJoin();

			if (!seen_before.contains(join) && !FilterAggregate.getTableName().equals(key_table_name)) {
				sql += " INNER JOIN " + join + " ";
				seen_before.add(join);
			}
		}

		// adding the WHERE clauses if applicable.
		int number_of_FilterAggregates = filterAggregates.size();
		if (number_of_FilterAggregates != 0) {
			sql += "WHERE ";

			// walking throuhg the FilterAggregates.
			for (int i = 0; i < number_of_FilterAggregates; i++) {
				String agg_wheres = ((FilterAggregate) filterAggregates.get(i))
						.getWheres();

				// dont need and AND for the first clause
				if (i == 0) {
					sql += " " + agg_wheres;
				}
				// need an AND infront for all additional clauses.
				else {
					sql += " AND " + agg_wheres;
				}
			}
		}

		// convert back the "filed name (TableName)" back to
		// "TableName.field_name"
		String sort_by_string = sort_by.getValue(sort_by.getSelectedIndex());
		sort_by_string = UpdateColumnStringToSql(sort_by_string);

		sql += " ORDER BY " + sort_by_string;

		String order = sort_asc_desc.getValue(sort_asc_desc.getSelectedIndex());
		String sql_order = order.startsWith("Asc") ? "ASC" : "DESC";

		sql += " " + sql_order;

		System.out.println(sql);
		return sql;
	}

	/**
	 * Retreives the right information from the database and populates table
	 * with it.
	 */
	public void populateTable() {

		clearData();

		String sql = constructSQL();

		ServiceAsync formsService = (ServiceAsync) GWT.create(Service.class);
		ServiceDefTarget target = (ServiceDefTarget) formsService;
		String relativeUrl = GWT.getModuleBaseURL() + FormData.getServletUrl();
		target.setServiceEntryPoint(relativeUrl);
		formsService.execteSQL(sql, start_record, start_record + record_chunk,
				new PopulateCallBack());
	}

	/**
	 * Retreives the right information from the database and populates table
	 * with it.
	 * 
	 * @param rows
	 *            the arraylist of rows containg array lists of columns
	 *            containing this tables data.
	 */
	public void populateTable(ArrayList rows) {

		System.out.println("Populating with rows: " + rows.size());

		// get the number of records from first arraylist
		int number_of_records = ((Integer) rows.get(0)).intValue();

		// update the showing label.
		createPageNavigation(number_of_records);

		this.add(table);
		table.populate(rows);
	}

	/**
	 * Sets up interface allowing user to view other records not showing up
	 * because of record per page limit
	 * 
	 * @param number_of_records
	 */
	private void createPageNavigation(int number_of_records) {

		record_navigation.clear();

		// set the label showing which page we are on
		int showing_end = start_record + record_chunk - 1;
		int end = showing_end > number_of_records ? number_of_records
				: showing_end;
		showing_records.setText("Showing Records " + start_record + "-" + end
				+ " out of " + number_of_records);

		// add buttons to navigate to other pages.
		Label go_to = new Label("Go to Page: ");
		go_to.setStyleName("sub_description_label");
		record_navigation.add(go_to);

		// adding buttons for navigation.
		int start_page = 1;
		int ceiling_page = (int) Math.ceil((number_of_records * 1.0)
				/ record_chunk);
		int current_page = start_record / record_chunk + 1;

		// remember the ... we place.
		boolean left_dots = false;
		boolean right_dots = false;

		for (int i = start_page; i <= ceiling_page; i++) {
			Label b = new Label(i + "");

			// we cannot show too many buttons, show only a few.
			if (i == 1 || i == ceiling_page
					|| ((i > current_page - 4) && (i < current_page + 4))) {
				// setting the proper style.
				if (i == current_page) {
					b.setStyleName("link_label_bold");
				} else {
					b.setStyleName("link_label");
				}

				// addin the click listener
				b.addClickListener(new ReportPageListener());
				record_navigation.add(b);
			}
			// adding the left ...
			else if (i != 1 && i != ceiling_page && i <= current_page - 4
					&& !left_dots) {
				b = new Label("...");
				b.setStyleName("link_label");
				record_navigation.add(b);
				left_dots = true;
			}
			// adding the right ...
			else if (i != 1 && i != ceiling_page && i >= current_page + 4
					&& !right_dots) {
				b = new Label("...");
				b.setStyleName("link_label");
				record_navigation.add(b);
				right_dots = true;
			}
		}
	}

	/**
	 * Removes all rows from table.
	 */
	private void clearData() {
		this.remove(table);
		table = new TableComponent(true, historyChangePrefix);
		table.setStyleName("advanced_search_table");
		table.setCellSpacing(0);

	}

	/**
	 * Callback to store the retreived db information
	 */
	private class PopulateCallBack implements AsyncCallback {
		public void onFailure(Throwable caught) {
			GWT.log("Error ", caught);
			caught.printStackTrace();
		}

		// store the given table names.
		public void onSuccess(Object result) {
			System.out.println("Call completed successfully");
			populateTable((ArrayList) result);
		}
	}

	/**
	 * This listener is attached to the button that registers new users.
	 */
	public class AddFilterAggregateListener implements ClickListener {
		public void onClick(Widget sender) {
			addNewFilter();
		}
	}

	/**
	 * Adds a new filter object to this panel.
	 */
	public void addNewFilter() {
		FilterAggregate f = new FilterAggregate(this, false);

		// adding the FilterAggregate to the list of FilterAggregates
		filterAggregates.add(f);

		// adding the FilterAggregate to the page
		add(f, filterAggregates.size());
	}

	/**
	 * Adds a new filter object to this panel.
	 */
	public void addNewFilter(BasicFilter filter) {
		FilterAggregate f = new FilterAggregate(this, true);

		f.setTableName(filter.getTableName());
		f.addFilter(filter);

		// adding the FilterAggregate to the list of FilterAggregates
		filterAggregates.add(f);

		// adding the FilterAggregate to the page
		add(f, filterAggregates.size());
	}

	/**
	 * This listener will populate the table based on filtered criteria set up
	 * by user
	 */
	public class UpdateListener implements ClickListener {
		public void onClick(Widget sender) {
			start_record = 1;
			populateTable();
		}
	}

	/**
	 * Listens to user clicks on the current page of report to display
	 */
	private class ReportPageListener implements ClickListener {
		public void onClick(Widget sender) {
			Label b = (Label) (sender);
			int number = Integer.parseInt(b.getText());
			start_record = (number - 1) * record_chunk + 1;
			populateTable();
		}
	}

	/**
	 * We received a notification, probably from a "another instance of?"
	 * component in this panel. Thus we notify any above panels that may contain
	 * this panel.
	 * 
	 * @param event
	 *            the message being passed by the observable.
	 */
	public void receiveNotification(FormComponent who) {

		// removing the FilterAggregate from the panel
		remove(who);

		// removing the FilterAggregate from the list of FilterAggregates
		filterAggregates.remove(who);

		// repopulate the table
		populateTable();
	}

	public void addColumn(String string) {
		sort_by.addItem(sqlToUpdateColumnString(string));
		column_names.add(string);
	}

	/**
	 * This takes in something like "Employees.employee_number" and returns a
	 * string like "employee number (Employees)"
	 * 
	 * @param name
	 * @return
	 */
	public String sqlToUpdateColumnString(String name) {

		// proccess this string if neccessary
		if (name.length() > 0 && name.indexOf('.') != -1
				&& name.indexOf('(') == -1) {
			// preparing formatting of table name
			String table_name = name.substring(0, name.indexOf('.'));
			table_name = "(" + table_name + ")";

			// preparing formatting of field name
			String field_name = name
					.substring(name.indexOf('.'), name.length());
			field_name = field_name.replace('_', ' ');
			field_name = field_name.replace('.', ' ');
			field_name = field_name.trim();

			return field_name + " " + table_name;
		}
		return name;
	}

	/**
	 * This takes in something like "employee number (Employees)" and returns a
	 * string like "Employees.employee_number"
	 * 
	 * @param name
	 * @return
	 */
	public String UpdateColumnStringToSql(String name) {

		// proccess this string if neccessary
		if (name.length() > 0 && name.indexOf('(') != -1) {

			// preparing formatting of table name
			String table_name = name.substring(name.indexOf('(') + 1, name
					.indexOf(')'));

			// preparing formatting of field name
			String field_name = name.substring(0, name.indexOf('('));
			field_name = field_name.trim();
			field_name = field_name.replace(' ', '_');

			return table_name + "." + field_name;
		}
		return name;
	}

	/**
	 * Updates the sortby dropdown
	 */
	public void updateColumns() {

		// reset current entries
		sort_by.clear();

		// need to add the pre-included sort bys
		for (int i = 0; i < column_names.size(); i++) {
			String name = (String) column_names.get(i);
			sort_by.addItem(sqlToUpdateColumnString(name));
		}

		// need to add columns from all filter aggregates
		for (int i = 0; i < filterAggregates.size(); i++) {
			ArrayList cols = ((FilterAggregate) filterAggregates.get(i))
					.getColumnNames();

			for (int j = 0; j < cols.size(); j++) {
				sort_by.addItem(sqlToUpdateColumnString((String) cols.get(j)));
			}
		}
	}

	/**
	 * When to focus is lost validate the field.
	 */
	public void onFocus(Widget sender) {
		String name = ((ListBox) sender).getStyleName();
		((ListBox) sender).setStyleName(name + "_focus");
	}

	/**
	 * When to focus is lost validate the field.
	 */
	public void onLostFocus(Widget sender) {

		String name = ((ListBox) sender).getStyleName();
		if (name.indexOf("_focus") != -1) {
			((ListBox) sender).setStyleName(name.substring(0, name.length()
					- "_focus".length()));
		}

		// update the records per page variable.
		if (sender == records_per_page) {
			record_chunk = Integer.parseInt(records_per_page
					.getItemText(records_per_page.getSelectedIndex()));
		}
	}

	/**
	 * Returns the filter aggregates on this widget.
	 * 
	 * @param index
	 *            the index of the desired aggregate.
	 * @return the filter aggregates on this widget.
	 */
	public FilterAggregate getAggregate(int index) {
		return (FilterAggregate) filterAggregates.get(index);
	}

	/**
	 * Removes an aggregate from this table
	 * 
	 * @param aggregate
	 *            the aggregate to be removed.
	 */
	public void removeAggregate(FilterAggregate aggregate) {
		this.remove(aggregate);
		filterAggregates.remove(aggregate);
		this.updateColumns();
	}
	
	
	/**
	 * Replaced the default filter of a certain table's column with
	 * another custom filter when it's field is selected
	 * @param field the field which should be replaced with custom filter
	 * @param filter the custom filter to replace the default filter.
	 */
	public void addReplacementFilter(String table_name, String column_name, BasicFilter filter) {
		fieldsToCustomFilters.put(table_name + column_name, filter);
	}
	
	/**
	 * Replaced the default filter of a certain table's column with
	 * another custom filter when it's field is selected
	 * @param field the field which should be replaced with custom filter
	 * @param filter the custom filter to replace the default filter.
	 */
	public BasicFilter getReplacementFilter(String table_name, String column_name) {
		return (BasicFilter)fieldsToCustomFilters.get(table_name.trim() + column_name.trim());
	}

	/**
	 * Returns the prefix that appears in the URL before the record name.
	 * 
	 * @return the prefix that appears in the URL before the record name.
	 */
	public String getHistoryChangePrefix() {
		return historyChangePrefix;
	}

	/**
	 * Sets the prefix that appears in the URL before the record name.
	 * 
	 * @param historyChangePrefix
	 *            the prefix in the URL.
	 */
	public void setHistoryChangePrefix(String historyChangePrefix) {
		this.historyChangePrefix = historyChangePrefix;
	}
}
